package com.sajus.web.billing.ejb.bean;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Set;

import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.ejb.TransactionManagement;
import javax.ejb.TransactionManagementType;

import org.apache.log4j.Logger;

import com.sajus.billing.client.delegate.BusinessDelegateFactory;
import com.sajus.billing.client.dto.AccountAttributeDTO;
import com.sajus.billing.client.dto.AccountDTO;
import com.sajus.billing.client.dto.BillDTO;
import com.sajus.billing.client.dto.BillItemDTO;
import com.sajus.billing.client.dto.ServiceAttributeDTO;
import com.sajus.billing.client.dto.ServiceDTO;
import com.sajus.billing.client.dto.UsageDataDTO;
import com.sajus.billing.client.dto.UsageItemLineDTO;
import com.sajus.billing.client.ejb.interfaces.AccountManagerLocal;
import com.sajus.billing.client.ejb.interfaces.AccountManagerRemote;
import com.sajus.web.billing.ejb.data.model.Account;
import com.sajus.web.billing.ejb.data.model.Bill;
import com.sajus.web.billing.ejb.data.model.BillItem;
import com.sajus.web.billing.ejb.data.model.PersistentObject;
import com.sajus.web.billing.ejb.data.model.Service;
import com.sajus.web.billing.ejb.data.model.ServiceAttribute;
import com.sajus.web.billing.ejb.data.model.UsageItemLine;
import com.sajus.web.billing.ejb.mapper.ModelToDTOMapper;

@Stateless(mappedName = "AccountManager")
@LocalBean
@TransactionManagement(TransactionManagementType.CONTAINER)
public class AccountManagerBean extends BaseBean implements AccountManagerLocal, AccountManagerRemote {

	private final int RECURRING = 1;
	private final int USAGE = 2;
	private String SIM_ID = "ICCID";

	private final String billQuery = "Bill.findCurrentIndex";
	private final String CURRENT_USAGE = "currentUsage";

	@Override
	public void createAccount(AccountDTO arg0) {

	}

	private void applyPreBillScripts(){

	}

	private void applyPostBillScripts(){
	}
	
	@Override
	public void setBillFeeAmount( AccountDTO acct ) {
		
		Account acc = super.getEntityByPK( Account.class, acct.getId() );
		Set<Account> accounts = acc.getChildrenAccount();
		Bill bill = getBillDomain( acc );
		if ( bill == null ) {
			return;
		}
		
		double amount = bill.getAmountDue();
		
		for ( Account account : accounts ) {
			Bill childBill = getBillDomain( account );
			if ( childBill != null ) {
				amount = amount + childBill.getAmountDue();
			}
		}
		bill.setAmountDue( amount );
		
		super.getEntityManager().merge( bill );
		
	}
	
	@Override
	public void createBill( AccountDTO acct ) {
		
		Date curDate = new Date();
		Calendar start = Calendar.getInstance(); 
		Calendar end = Calendar.getInstance(); 
		
		Account acc = super.getEntityByPK( Account.class, acct.getId() );
		
		if ( curDate.before( acc.getNextBillingDate() ) ){
			return;
		} else {
			
			start.setTime( acc.getNextBillingDate() );
			start.add( Calendar.MONTH, -1 );
			start.set( Calendar.DAY_OF_MONTH,start.getActualMinimum(Calendar.DAY_OF_MONTH) );
			
			end.setTime( acc.getNextBillingDate() );
			end.add( Calendar.MONTH, -1 );
			end.set( Calendar.DAY_OF_MONTH, end.getActualMaximum( Calendar.DAY_OF_MONTH) );
		}
		
		Bill bill = new Bill();

		bill.setAccount( acc );
		
		bill.setBillingPeriodEnd( end.getTime() );
		bill.setBillingPeriodStart( start.getTime() );

		bill.setCurrency( "CAD" );

		try {
			//grab and seperate the recurring from usage;
			List<Service> fullServices = getServices( acc );

			ArrayList<BillItem> billItems = new ArrayList<BillItem>();
			int index = 1;
			for ( Service service : fullServices ){
				
				if ( service.getOffering().getServiceType() != null 
						&& service.getOffering().getServiceType().getType() != null ){

					System.out.println(" Service id = " + service.getId() );
					BillItem billItem = generateBillItem( service, index, start, end );
					billItem.setBill( bill );

					billItems.add( billItem );
					
					// Remove the Current usage:
					for ( ServiceAttribute sa : service.getServiceAttributes() ){
						if ( sa.getName() != null && sa.getName().equalsIgnoreCase( CURRENT_USAGE ) ){
							sa.setValue( "0.0" );
							super.getEntityManager().merge( sa );
						}
					}
				}
				index++;
			}
			double fee = 0d;
			for ( BillItem item : billItems ){
				fee = fee + item.getAmount();
			}

			bill.setItems( billItems );
			bill.setAmountDue( fee );

			System.out.println("About to save Billing Fee = " + fee + " end date = " 
			+ end.getTime()  + " start date = " + start.getTime() );
			
			//Persisting each bill and bill item.
			
			bill.setDate( new Date() );
			
			saveBill( bill );
			
			Calendar cal = Calendar.getInstance(); 
			cal.setTime( acc.getNextBillingDate() );
			cal.add(Calendar.MONTH, 1);

			acc.setNextBillingDate( cal.getTime() );
			saveAccount( acc );
		} catch ( Exception e ){
			Logger.getLogger( AccountManagerBean.class ).error( "Error with generating bill " + e.getMessage() );
		}
	}

	private BillItem generateBillItem(Service service, int index, Calendar firstDay, Calendar lastDay ){

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		BillItem ret = new BillItem();

		ret.setDescription( service.getOffering().getServiceType().getDescription() );
		//	ret.setDiscountRate(discountRate)
		ret.setLineNumber( index );
		ret.setType( service.getOffering().getServiceType().getType() );

		if ( service.getOffering().getServiceType() != null 
				&& service.getOffering().getServiceType().getType().intValue() == RECURRING ){

			ret.setAmount( service.getOffering().getBaseRate() );

		} else if ( service.getOffering().getServiceType() != null 
				&& service.getOffering().getServiceType().getType().intValue() == USAGE ) {
			
			// Usage billing item 
			Connection con = null;
			try {
				String url = "jdbc:mysql://localhost:3306/domain1";
				String user = "bpm";
				String password = "bpm22";

				con = DriverManager.getConnection(url, user, password);

				List<ServiceAttribute> saList = service.getServiceAttributes();
				String sim = "";
				double charge = 0d;
				
				String tableName = service.getOffering().getServiceType().getServicetable();
				String columnName = service.getOffering().getServiceType().getServicecolumn();
				String serviceId = service.getOffering().getServiceType().getIdentifier();
				String channelId = service.getOffering().getServiceType().getChannelId();
				
				for( ServiceAttribute sa : saList ){
					if ( sa.getName().equals( channelId ) ){
						sim = sa.getValue();
					}
				}

				System.out.println("sim = " + sim );

				String usageQuery = "SELECT * FROM " + tableName + " WHERE " + serviceId + " = '" 
						+ sim + "' AND charged_date between '" + sdf.format( firstDay.getTime() ) 
						+ "' AND '" + sdf.format( lastDay.getTime() ) + "' ";

				System.out.println( "Created query = " + usageQuery );
				Statement usageStatement = con.createStatement();
				ResultSet usageDetailsSet = usageStatement.executeQuery( usageQuery );
				System.out.println( serviceId + " = " + sim );

				int usage = 0;
				String usageIdentifier = "";
				ArrayList<UsageItemLine> usageList = new ArrayList<UsageItemLine>();
				
				if ( columnName != null && !columnName.trim().equals( "" ) ){
					
					while ( usageDetailsSet.next() ){
						
						UsageItemLine usageLine = new UsageItemLine();
						usageLine.setCharge( usageDetailsSet.getDouble( "charge" ) );
						usageLine.setCharge_dt( usageDetailsSet.getDate( "charged_date" ) );
						
						usageLine.setUsages( usageDetailsSet.getInt( columnName ) );
						
						usageLine.setVar0value( usageDetailsSet.getDate( "usage_date" ).toGMTString() );
						usageLine.setVar1value( usageDetailsSet.getString( "phone_num" ) );
						usageLine.setVar2value( usageDetailsSet.getString( "sim_num" ) );
						usageLine.setVar3value( service.getOffering().getDescription() );
						
						usage = usage + usageDetailsSet.getInt( columnName );
						usageIdentifier = usageDetailsSet.getString( serviceId );
						usageLine.setBillItem( ret );
						charge = charge + usageLine.getCharge();
						usageLine.setBillItem( ret );
						usageList.add( usageLine );
					}
				} else {
					
					while ( usageDetailsSet.next() ){
						UsageItemLine usageLine = new UsageItemLine();
						usageLine.setCharge( usageDetailsSet.getDouble( "charge" ) );
						usageLine.setCharge_dt( usageDetailsSet.getDate( "charged_date" ) );
						
						usageLine.setUsages( 1d );
						
						usageLine.setVar0value( usageDetailsSet.getDate( "usage_date" ).toGMTString() );
						usageLine.setVar1value( usageDetailsSet.getString( "phone_num" ) );
						usageLine.setVar2value( usageDetailsSet.getString( "sim_num" ) );
						
						usage = usage + 1;
						usageIdentifier = usageDetailsSet.getString( serviceId );
						usageLine.setBillItem( ret );
						charge = charge + usageLine.getCharge();
						
						usageList.add( usageLine );
						usageLine.setBillItem( ret );
					}
				}
				
				ret.setUsageLineItems( usageList );
				ret.setAmount( charge );

			} catch ( Exception e ){
				e.printStackTrace();
			} finally {
				try {
					con.close();
				} catch ( Exception e ){
					Logger.getLogger( AccountManagerBean.class ).error( "Failed to close connection " + e.getMessage() );
				}
			}
		}
		return ret;
	}
	
	@Override
	public void createBillItem(ServiceDTO service) {

	}

	@Override
	public AccountDTO getAccount(Integer id) {
		return ModelToDTOMapper.map( super.getEntityByPK( Account.class, id) );
	}
	
	@Override
	public List<AccountAttributeDTO> getAccountAttributes(AccountDTO arg0) {
		return null;
	}

	@Override
	public List<AccountDTO> getSubAccounts(AccountDTO arg0) {
		return null;
	}

	@Override
	public void saveAccount(AccountDTO acc) {
//		Account account = ModelToDTOMapper.map( acc );
//		super.update( account );
		
		System.out.println("Account Id " + acc.getId() );
		if ( acc.getId() == null ){
			try {
				super.getEntityManager().merge( ModelToDTOMapper.map( acc ) );
			} catch ( Exception e ){
				e.printStackTrace();
			}
		} else {
			super.update( ModelToDTOMapper.map( acc ) );
		}
	}

	@Override
	public void saveAccountAttribute(AccountAttributeDTO acc) {
		System.out.println("account attribute id " + acc.getId() );
		if ( acc.getId() == null ){
			try {
				super.getEntityManager().merge( ModelToDTOMapper.map( acc ) );
			} catch ( Exception e ){
				e.printStackTrace();
			}
		} else {
			super.update( ModelToDTOMapper.map( acc ) );
		}
	}

	@Override
	public BillDTO getBill( AccountDTO account) {
		return ModelToDTOMapper.map( super.getSingleResult("Bill.findByAccount", Bill.class, ModelToDTOMapper.map( account ) ) );
	}
	
	public Bill getBillDomain( Account account) {
		return super.getSingleResult("Bill.findByAccount", Bill.class, account );
	}
	
	@Override
	public List<BillDTO> getBills( Integer id ) {
		AccountDTO acc = getAccount( id );
		if ( acc != null ) {
			List<BillDTO> ret = new ArrayList<BillDTO>();
			List<Bill> bills = super.getResultList( "Bill.findAllWithAccount", Bill.class, ModelToDTOMapper.map( acc ) ); 
			if ( bills != null ){
				for ( Bill bill : bills ){
					ret.add( ModelToDTOMapper.map( bill ) );
				}
			}
			
			return ret;
		}
		return null;
	}

	@Override
	public BillItemDTO getBillItem(Integer id) {
		return ModelToDTOMapper.map( super.getEntityByPK( BillItem.class, id) );
	}

	@Override
	public List<AccountDTO> getAccounts(){
		List<Account> accounts = super.getResultList("Account.findAll", Account.class );
		List<AccountDTO> ret = new ArrayList<AccountDTO>();

		for ( Account account : accounts ){
			ret.add( ModelToDTOMapper.map( account ) );
		}

		return ret;
	}

	private int generateIDNumber(String query, Class cls) {
		PersistentObject ser = super.getSingleResult(query, cls );

		if ( ser == null ){
			System.out.println("Table was empty " + cls.toString() );
			return 1;
		} else {
			return ser.getId() + 1;
		}
	}

	@Override
	public List<UsageDataDTO> getUsageDataList(AccountDTO account ) {
		
		List<UsageDataDTO> ret = new ArrayList<UsageDataDTO>();
		BillDTO bill = getBill(account);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		
		for ( ServiceDTO service : account.getServices() ){
			
			if ( service.getOffering().getServiceType() != null 
					&& service.getOffering().getServiceType().getType().intValue() == USAGE ) {

				try {
					
					String url = "jdbc:mysql://localhost:3306/domain1";
					String user = "root";
					String password = "bpm22";
					Connection con = null;

					con = DriverManager.getConnection(url, user, password);

					List<ServiceAttributeDTO> saList = service.getServiceAttributes();
					String sim = "";
					String rateKey = "";
					double charge = 0d;

					for( ServiceAttributeDTO sa : saList ){
						if ( sa.getName().equals( SIM_ID ) ){
							sim = sa.getValue();
						}
					}

					String tableName = service.getOffering().getServiceType().getServicetable();
					String columnName = service.getOffering().getServiceType().getServicecolumn();

					System.out.println("sim = " + sim );

					String smsUseSql = "SELECT * FROM " + tableName + " WHERE sim_num = '" 
							+ sim + "' AND charged_date between '" + sdf.format( bill.getBillingPeriodStart() ) + "' AND '" 
							+ sdf.format( bill.getBillingPeriodEnd() ) + "' ";

					System.out.println( "Created query = " + smsUseSql );
					Statement st3 = con.createStatement();
					ResultSet usageDetailsSet = st3.executeQuery( smsUseSql );
					System.out.println( "simNum = " + sim );

					int usage = 0;

					if ( columnName != null && !columnName.trim().equals( "" ) ){
						while ( usageDetailsSet.next() ){
							UsageDataDTO cr = new UsageDataDTO();
							cr.setCharge( usageDetailsSet.getDouble("charge") );
							cr.setChargeDate( usageDetailsSet.getDate("charged_date") );
							cr.setId( usageDetailsSet.getString( "sim_num") );
							cr.setNumberCalled( usageDetailsSet.getString("number_called") );
							cr.setPhoneNum( usageDetailsSet.getString("phone_num") );
							cr.setUsage( usageDetailsSet.getDouble( columnName ) );
							cr.setRateKey( usageDetailsSet.getString( "ratekey" ) ) ;
							cr.setUsageDate( usageDetailsSet.getDate( "usage_date") );
							cr.setUsageTime( usageDetailsSet.getDate( "usage_time") );
							cr.setTableName(tableName);
							cr.setColumnName(columnName);
							ret.add(cr);
						}
					} else {
						while ( usageDetailsSet.next() ){
							UsageDataDTO cr = new UsageDataDTO();
							cr.setCharge( usageDetailsSet.getDouble("charge") );
							cr.setChargeDate( usageDetailsSet.getDate("charged_date") );
							cr.setId( usageDetailsSet.getString( "sim_num") );
							cr.setNumberCalled( usageDetailsSet.getString("number_called") );
							cr.setPhoneNum( usageDetailsSet.getString("phone_num") );
							cr.setRateKey( usageDetailsSet.getString( "ratekey" ) ) ;
							cr.setUsageDate( usageDetailsSet.getDate( "usage_date") );
							cr.setUsageTime( usageDetailsSet.getDate( "usage_time") );
							cr.setTableName(tableName);
							cr.setColumnName( service.getOffering().getServiceType().getUnit() );
							
							ret.add(cr);
						}
					}

				} catch ( Exception e ){
					e.printStackTrace();
				}
			}
		}
		return ret;
	}

//	private void saveUsageItemLine( UsageItemLineDTO usage ){
//		if ( usage.getId() == null ){
//			try {
//				super.getEntityManager().merge( ModelToDTOMapper.map( usage ) );
//			} catch ( Exception e ){
//				Logger.getLogger( AccountManagerBean.class ).error( "Failed to save usage " + usage.getCharge() );
//				Logger.getLogger( AccountManagerBean.class ).error( "Failed to save usage " + e.getMessage() );
//			}
//		} else {
//			super.update( ModelToDTOMapper.map( usage ) );
//		}
//	}
//	
//	private void saveBillItem( BillItemDTO billItem ){
//		if ( billItem.getId() == null ){
//			if ( billItem.getBill() != null ) {
//				System.out.println("service id " + billItem.getBill().getId() );
//			}
//			try {
//				super.getEntityManager().merge( ModelToDTOMapper.map( billItem ) );
//			} catch ( Exception e ){
//			}
//		} else {
//			super.update( ModelToDTOMapper.map( billItem ) );
//		}
//	}
	
	private void saveBill( BillDTO bill ){
		if ( bill.getId() == null ){
			try {
				super.getEntityManager().merge( ModelToDTOMapper.map( bill ) );
			} catch ( Exception e ){
			}
		} else {
			super.update( ModelToDTOMapper.map( bill ) );
		}
	}
	
	private void saveAccount( Account acc ){
		if ( acc.getId() == null ){
			super.getEntityManager().merge( acc );
		} else {
			super.update( acc );
		}
	}
	
	
	private void saveBill( Bill bill ){
		if ( bill.getId() == null ){
			try {
				super.getEntityManager().merge( bill );
			} catch ( Exception e ){
			}
		} else {
			super.update( bill );
		}
	}
	
	private List<Service> getServices( Account acc ) {

		List<Service> services = super.getResultList( "Service.findServicesByAccount" 
				, Service.class, acc );
		List<Service> ret = new ArrayList<Service>();
		if ( services != null ) {
			for ( Service service : services ) {
				System.out.println("getServices: service  id " + service.getId() );
				ret.add( service );
			}
		}

		return ret;
	}
}
